VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsSearch"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True

'recordsets
Dim rsRequestList As ADODB.recordSet
Dim rsHasRights As ADODB.recordSet
Dim rsXMLType As ADODB.recordSet
Dim rsMQStatus As ADODB.recordSet
Dim rsComID As ADODB.recordSet
Dim rsBUNI As ADODB.recordSet
Dim rsProcessStatus As ADODB.recordSet
Dim rsFormType As ADODB.recordSet


Private Enum vbErrorNumbers
    KT_STD_ERROR = vbObjectError + 512
    KT_INIT_SEARCH = vbObjectError + 513
    KT_PERFORMUSERSEARCH = vbObjectError + 514
    KT_PERFORMSEARCH = vbObjectError + 515
    KT_DBCONNECT = vbObjectError + 516
    KT_INIT_USERSEARCH = vbObjectError + 517
    KT_FIND_BULLETINS = vbObjectError + 518
    KT_GET_BULLETINS = vbObjectError + 519
    KT_CREATE_NEW_BULLETIN = vbObjectError + 520
    KT_GET_ALL_COMPANIES = vbObjectError + 521
    KT_GET_DIRECTIVE_DETAILS = vbObjectError + 522
End Enum

Option Explicit
Public Sub initSearch(ByVal strSearchBy As String, _
                            ByVal strSearchFor As String, _
                            ByVal strNextPre As String, _
                            ByVal strComId As String, _
                            ByRef iCurrentPage As Variant, _
                            ByRef iMaxPages As Variant, _
                            ByVal strUsername As String, _
                            ByVal strDateFrom As String, _
                            ByVal strDateTo As String, _
                            Optional ByVal varRowsPerPage As Variant)
                            
    On Error GoTo PROC_ERROR
    
    Dim obj_ADOConn As ADODB.Connection
    Dim strSQL
    
    
    Dim iMaxPagesHolder As Integer
    Dim iCurrentPageHolder As Integer
    Dim iRowsPerPage  As Integer
    
    Dim stryear As String
    Dim strMonth As String
    Dim strDay As String
   
    
    'open the connection to the database
    Set obj_ADOConn = New ADODB.Connection
    obj_ADOConn.ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
    obj_ADOConn.Open
    
    Set rsRequestList = New ADODB.recordSet
    
    'validate the rows per page variable from the web
    If CStr(varRowsPerPage) = "" Then
        iRowsPerPage = 20
    Else
        If CInt(varRowsPerPage) = 0 Then
            iRowsPerPage = 20
        Else
            If IsNumeric(varRowsPerPage) = True Then
                iRowsPerPage = CInt(varRowsPerPage)
            Else
                iRowsPerPage = 20
            End If
        End If
    End If
      
    Set rsRequestList = New ADODB.recordSet
    
    iCurrentPageHolder = CInt(iCurrentPage)
    iMaxPagesHolder = CInt(iMaxPagesHolder)
    
    'the cursor must support bookmarks for us to use the pageing functionallity
    rsRequestList.CursorType = adOpenStatic
    rsRequestList.CursorLocation = adUseClient
    rsRequestList.PageSize = iRowsPerPage
    rsRequestList.CacheSize = iRowsPerPage 'set the cached number of records = 20
    
    'Convert Created_Date to date format
    If strSearchBy = "CREATED_DATE" Then
        
        strSQL = "select * from dir_interface_index where Convert(varchar," & strSearchBy & ",112) >= '" & strDateFrom & "' and Convert(varchar," & strSearchBy & ",112) <= '" & strDateTo & "' order by interface_no"
        'strSQL = "select * from dir_interface_index where Convert(varchar," & strSearchBy & ",112) = '" & strDateFrom & "' order by interface_no"
        rsRequestList.Open strSQL, obj_ADOConn, adOpenStatic, adLockBatchOptimistic
   Else
        'strSQL = "exec dir_list_requests '" & strComId & "','" & strSearchFor & "','" & strSearchBy & "'"
        strSQL = "Select * from dir_interface_index where " & strSearchBy & " = '" & strSearchFor & "' order by interface_no"
        rsRequestList.Open strSQL, obj_ADOConn, adOpenStatic, adLockBatchOptimistic
   End If
    
    If rsRequestList.EOF = True And rsRequestList.BOF = True Then
        'there were no records returned for the search, so return -1 for
        'the current page to let the web page know to display the "no results found" string
        iCurrentPage = -1
        Exit Sub
    Else
        iMaxPagesHolder = rsRequestList.PageCount
        'manage the current page var
        If iCurrentPageHolder < 1 Then
            iCurrentPageHolder = 1
        End If
    
        If iCurrentPageHolder > iMaxPagesHolder Then
            iCurrentPageHolder = iMaxPagesHolder
        End If
        
        'pass out the integers
        iCurrentPage = CStr(iCurrentPageHolder)
        iMaxPages = CStr(iMaxPagesHolder)
    End If
    
    'disconnect the recordset to free up resources
 '   rsRequestList.ActiveConnection = Nothing
    Exit Sub
PROC_ERROR:
        Err.Raise vbErrorNumbers.KT_INIT_SEARCH, "Keytax.clsSearch.initSearch", Err.Description
End Sub
Public Function PerformSearch(ByVal strSearchBy As String, _
                       ByVal strSearchFor As String, _
                       ByVal strNextPre As String, _
                       ByRef strErrMSG As Variant, _
                       ByRef iCurrentPage As Variant, _
                       ByVal strUsername As String) As String
                       
    On Error GoTo PROC_ERROR
    
    'this sub will perform the search by calling the relevant stored procs in a loop
    'and creates the built table in HTML with everything in it.
    Dim obj_ADOConn As ADODB.Connection
    Dim strPAYEHolder As String
    Dim strDetHolder As String
    Dim strHTMLHolder As String
    Dim strSQL As String
    Dim strIDHolder As String
    Dim strWhere As String
    Dim strFundNoHolder As String
    Dim strIAm As String
    Dim strIAmId As String
    Dim strIWantTO As String
    Dim strCanYN As String
    Dim sHolderAnt As String
    Dim directive_number As String
    Dim Taxpayer As String
    Dim birthdate As String
    Dim req_on As String
    Dim req_by As String
    Dim meaning As String
    
    Dim nIntNo As String
    Dim nComID As String
    Dim nBunID As String
    Dim nMsgID As String
    Dim nRefID As String
    Dim nReqSeqNum As String
    Dim strDate As String
    Dim strResYN As String
    Dim nKeytaxType As String
    Dim nMQStat As String
    Dim nStatus As String
        
    Dim strComId As String
    Dim strBUNI As String
    Dim strXMLType As String
    Dim strMQStatus As String
    Dim strProcessStatus As String
    Dim strFormType As String
    
    Dim iCounter As Integer
    
    'open the connection to the database
    Set obj_ADOConn = New ADODB.Connection
    obj_ADOConn.ConnectionString = GetConnectionString() 'Johann (20090625): Centralised code for connection string added
    obj_ADOConn.Open
    
    'Set Objects
    Set rsHasRights = New ADODB.recordSet
    Set rsXMLType = New ADODB.recordSet
    Set rsMQStatus = New ADODB.recordSet
    Set rsComID = New ADODB.recordSet
    Set rsBUNI = New ADODB.recordSet
    Set rsProcessStatus = New ADODB.recordSet
    Set rsFormType = New ADODB.recordSet

    strErrMSG = ""
    
'load the users access rights for the anchor
strSQL = "select use_id from dir_users where username = '" & strUsername & "'"
rsHasRights.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
strIAm = rsHasRights("use_id")
rsHasRights.Close

strSQL = "select use_id from dir_contact_details where use_id = " & strIAm
rsHasRights.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
strIWantTO = rsHasRights("use_id")
rsHasRights.Close

strSQL = "exec dir_check_load_user " & strIAm & "," & strIWantTO
rsHasRights.Open strSQL, obj_ADOConn
strCanYN = rsHasRights("see_user")
rsHasRights.Close

'the user can see interface status details if they have access
If strIAm = strIWantTO Then
    strCanYN = "Y"
End If
        
    'check the recordset to see if its empty!
    If rsRequestList.BOF = True And rsRequestList.EOF = True Then
        'generate the HTML to hold the returned info
        
        strHTMLHolder = "" & _
            "<tr>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
                "<td class=FormLeftCell valign=top>No information found</td>" & _
            "</tr>"
                
        iCurrentPage = -1
        'the recordset is empty!
        PerformSearch = strHTMLHolder
        Exit Function
    End If
    'disallow the browseing past the beginning or the end of the set, lower than 1 or
    'greater than page count
        
    rsRequestList.AbsolutePage = CLng(iCurrentPage)
    
    'get the first id in the recordset
    
    'loop to the end of the page
    Do While rsRequestList.EOF = False And rsRequestList.AbsolutePage = iCurrentPage
        
        'shane: New code added 2002/09/04
        
        'Get the FormType for the HREF parameter being passed to the asp page
        If IsNull(rsRequestList.Fields("req_seq_num").Value) = False Then
            strSQL = "select scod_form_type from dir_request where req_seq_num = " & rsRequestList.Fields("req_seq_num").Value
            rsFormType.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
            strFormType = rsFormType("scod_form_type")
            rsFormType.Close
        End If
        
        If IsNull(rsRequestList.Fields("interface_no").Value) = True Then
            nIntNo = "n/a"
        Else
            nIntNo = rsRequestList.Fields("interface_no").Value
        End If
                        
        'Get the Company ID text description
        strSQL = "select code from dir_companies where com_id = " & rsRequestList.Fields("com_id").Value
        rsComID.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
        strComId = rsComID.Fields("code").Value
        rsComID.Close
        Select Case strComId
            Case "LG"
                strComId = "Liberty Group"
        End Select
        
        If IsNull(rsRequestList.Fields("com_id").Value) = True Then
            nComID = "n/a"
        Else
            nComID = strComId
        End If
        
        'Get Business Unit ID text description
        'Shane: For test purposes only
        If rsRequestList.Fields("bu_id").Value = "LCB01" Then
            Dim strTemp As String
            strTemp = "LCB"
            strSQL = "select description from dir_business_units where code = '" & strTemp & "'"
        Else
            strSQL = "select description from dir_business_units where code = '" & rsRequestList.Fields("bu_id").Value & "'"
        End If
        rsBUNI.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
        strBUNI = rsBUNI.Fields("description").Value
        rsBUNI.Close
        
        If IsNull(rsRequestList.Fields("bu_id").Value) = True Then
            nBunID = "n/a"
        Else
            nBunID = strBUNI
        End If
        
        If IsNull(rsRequestList.Fields("message_id").Value) = True Then
            nMsgID = "n/a"
        Else
            nMsgID = rsRequestList.Fields("message_id").Value
        End If
        
        If IsNull(rsRequestList.Fields("reference_id").Value) = True Then
           nRefID = "n/a"
        Else
            nRefID = rsRequestList.Fields("reference_id").Value
        End If
        
        If IsNull(rsRequestList.Fields("req_seq_num").Value) = True Then
            nReqSeqNum = "n/a"
        Else
            nReqSeqNum = rsRequestList.Fields("req_seq_num").Value
        End If
        
        If IsNull(rsRequestList.Fields("created_date").Value) = True Then
            strDate = "n/a"
        Else
            strDate = rsRequestList.Fields("created_date").Value
        End If
        
        If IsNull(rsRequestList.Fields("sars_response_yn").Value) = True Then
            strResYN = "n/a"
        Else
            strResYN = rsRequestList.Fields("sars_response_yn").Value
        End If
        
        'Get text description for keytax_type code
        strSQL = "select meaning from dir_system_codes where code = 'XML_TYPE' and value = '" & rsRequestList.Fields("keytax_type").Value & "'"
        rsXMLType.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
        strXMLType = rsXMLType.Fields("meaning").Value
        rsXMLType.Close
        
        If IsNull(rsRequestList.Fields("keytax_type").Value) = True Then
            nKeytaxType = "n/a"
        Else
            nKeytaxType = strXMLType
        End If
        
        'Get text description for MQ_status code
        strSQL = "select meaning from dir_system_codes where code = 'MQ_STATUS' and value = '" & rsRequestList.Fields("mq_status").Value & "'"
        rsMQStatus.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
        strMQStatus = rsMQStatus.Fields("meaning").Value
        rsMQStatus.Close
        
        If IsNull(rsRequestList.Fields("mq_status").Value) = True Then
            nMQStat = "n/a"
        Else
            nMQStat = strMQStatus
        End If
        
        'Get text description for process status
        If strResYN = "N" Then
            strSQL = "select meaning from dir_system_codes where value in (select scod_process_status from dir_request where req_seq_num in (Select req_seq_num from dir_interface_index where req_seq_num = '" & nReqSeqNum & "' and sars_response_yn = 'N'))and code = 'PROCESS_STATUS'"
            rsProcessStatus.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
            strProcessStatus = rsProcessStatus.Fields("meaning").Value
            
            If IsNull(rsProcessStatus.Fields("meaning").Value) = True Then
                nStatus = "n/a"
            Else
                nStatus = strProcessStatus
            End If
            rsProcessStatus.Close
        ElseIf strResYN = "Y" Then
            strSQL = "select meaning from dir_system_codes where value in (select request_status from dir_response where req_seq_num in (Select req_seq_num from dir_interface_index where req_seq_num = '" & nReqSeqNum & "' and sars_response_yn = 'Y')) and code = 'DIR_REQ_STATUS'"
            rsProcessStatus.Open strSQL, obj_ADOConn, adOpenStatic, adLockOptimistic
            strProcessStatus = rsProcessStatus.Fields("meaning").Value
            
            If IsNull(rsProcessStatus.Fields("meaning").Value) = True Then
                nStatus = "n/a"
            Else
                nStatus = strProcessStatus
            End If
            rsProcessStatus.Close
        Else
            nStatus = "n/a"
        End If
        
        iCounter = 1
        strHTMLHolder = strHTMLHolder & "<tr>"
        iCounter = 1
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & nIntNo & "</td>"
        iCounter = 2
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & nComID & "</td>"
        iCounter = 3
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & nBunID & "</td>"
        iCounter = 4
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & nMsgID & "</td>"
        iCounter = 5
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & nRefID & "</td>"
        iCounter = 6
        If UCase(strCanYN) = "Y" Then
            If nReqSeqNum <> "n/a" Then
                strHTMLHolder = strHTMLHolder & "" & _
                    "<td class=FormLeftCell valign=top style=""width:6%""><A href=""../RequestInfo/Req_Info.asp?nReqSeqNum=" & CStr(nReqSeqNum) & "&sFormType=" & CStr(strFormType) & """>" & CStr(nReqSeqNum) & "</A></td> "
            Else
                strHTMLHolder = strHTMLHolder & "" & _
                    "<td class=FormLeftCell valign=top style=""width:6%"">" & nReqSeqNum & "</td> "
            End If
        End If
'        If UCase(strCanYN) = "Y" Then
'                strHTMLHolder = strHTMLHolder & "" & _
'                    "<td class=FormLeftCell valign=top style=""width:6%""><A href=""../RequestInfo/Req_Info.asp?nReqSeqNum=" & CStr(nReqSeqNum) & "&sFormType=" & CStr(strFormType) & """>" & CStr(nReqSeqNum) & "</A></td> "
'        End If
        'strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & nReqSeqNum & "</td>"
        iCounter = 7
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & strDate & "</td>"
        iCounter = 8
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & strResYN & "</td>"
        iCounter = 9
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:11%"">" & nKeytaxType & "</td>"
        iCounter = 10
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & nMQStat & "</td>"
        iCounter = 11
        strHTMLHolder = strHTMLHolder & "<td class=FormLeftCell valign=top style=""width:10%"">" & nStatus & "</td>"
        iCounter = 12
        strHTMLHolder = strHTMLHolder & "</tr>"
        iCounter = 13
           
        rsRequestList.MoveNext
    Loop

                
        iCurrentPage = -1
        'the recordset is empty!
        PerformSearch = strHTMLHolder

    
    PerformSearch = strHTMLHolder
    obj_ADOConn.Close
    Set obj_ADOConn = Nothing
    Set rsRequestList = Nothing
    Set rsHasRights = Nothing
    Set rsXMLType = Nothing
    Set rsMQStatus = Nothing
    Set rsComID = Nothing
    Set rsBUNI = Nothing
    Set rsProcessStatus = Nothing
    Set rsFormType = Nothing

    'Set rsComID = Nothing
   Exit Function
PROC_ERROR:
    App.LogEvent ("Null error for clsSearch" & Now & "Line number last reached_really!!:" & iCounter & "." & "String = " & strHTMLHolder)
    Err.Raise vbErrorNumbers.KT_PERFORMSEARCH, "Keytax.clsSearch.PerformSearch", Err.Description & "Null error for clsSearch" & Now & "Line number last reached:" & iCounter & "." & "String = " & strHTMLHolder & "Req by: " & CStr(rsRequestList("req_by"))
End Function





